import json
import os

import pymysql
from bs4 import BeautifulSoup
from config import DIR_PATH


# 读取json
def read_json(filename, key):
    # 文件路径
    file_path = DIR_PATH + os.sep + "data" + os.sep + filename
    arr = []
    with open(file_path, "r", encoding="utf-8")as f:
        for data in json.load(f).get(key):
            arr.append(tuple(data.values())[1:])
        # 返回位置
        return arr


def parser_html(resp):
    #     提取thml
    html = resp.json().get("description").get("form")
    # 获取soup对象
    soup = BeautifulSoup(html, "html.parser")
    # 提取url
    url = soup.form.get("action")
    # 提取所有input标签(name&value)
    dict = {}
    for input in soup.find_all("input"):
        dict[input.get("name")] = input.get("value")
    #     返回url和dict

    return url, dict

# 连接数据库
def exe_database(sql):
    cursor = None
    coon = None
    try:
    #     获取连接对象
        coon=pymysql.connect(host="121.43.169.97",user="root",
                             password="Itcast_p2p_20191228",
                             database="czbk_member",
                             port=3306,charset="utf8",autocommit=True)

    # 获取游标对象
        cursor=coon.cursor()
    # 执行sql语句
        cursor.execute(sql)
    # 返回数据
        if sql.lower().split(" ")[0]=="select":
            # 查询返回所有结果
            return cursor.fetchall()
        else:
    #         非查询返回受影响数
            return cursor.rowcount
    except:
#         回滚
        coon.rollback()
    finally:
        cursor.close()
        coon.close()

#         清除数据
def clear_data():
    sql1 = """
        delete i.* from mb_member_info i inner join mb_member m on i.member_id=m.id where m.phone in ("13600003370","13600003371","13600001111","13600001112");
        """
    exe_database(sql1)
    sql2 = """
            delete l.* from mb_member_login_log l inner join mb_member m on l.member_id=m.id where m.phone in ("13600003370","13600003371","13600001111","13600001112");
            """
    exe_database(sql2)
    sql3 = """
            delete from mb_member_register_log where phone in ("13600003370","13600003371","13600001111","13600001112");
            """
    exe_database(sql3)
    sql4 = """
            delete from mb_member where phone in ("13600003370","13600003371","13600001111","13600001112");
            """
    exe_database(sql4)




if __name__ == '__main__':
    print(read_json("register_login.json", "register"))
